﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DaInterFace;

namespace DaFunction
{
    internal class DataBase : IDataBase
    {
        private Dictionary<String, Account> Accounts = new Dictionary<String, Account>();
        private Dictionary<String, SetOfBook> SetOfBooks = new Dictionary<String, SetOfBook>();
        private String _connection;

        public event DataBaseMessage MessageEvent;

        //string strAccountISN = "";
        //字典—销售账户
        //public static Dictionary<string, SalesAccount> dsa = new Dictionary<string, SalesAccount>();

        public DataBase(String connection)
        {
            _connection = connection;
            FillAccounts();
        }
        public String GetConnection()
        {
            return _connection;
        }
        public Boolean Test()
        {
            try
            {
                //SqlParameter[] sps = new SqlParameter[1];
                //sps[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
                //sps[0].Value = "0000";

                //String sql = "SELECT FText Pswd,FCheckBox1 IMEI FROM KEW_SalesGuests WHERE FCheckBox=1 AND FBillNo=@UserName\r\nSELECT FText2 IMEI FROM KEW_SalesGuestsIMEI WHERE FID =(SELECT FID FROM KEW_SalesGuests WHERE FCheckBox=1 AND FBillNo=@UserName)";
                //Recordset rs = ExeProcedure("2", sql, sps);


                DataSet data = new DataSet();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT GetDate() ServerTime";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DateTime now = DateTime.Now;
                adapter.Fill(data);
                String time = data.Tables[0].Rows[0]["ServerTime"].ToString();

                String note = "{主服务器连接正常} ";
                
                foreach (Account account in Accounts.Values)
                {
                    try
                    {
                        now = DateTime.Now;
                        cmd.Connection = conn;
                        adapter = new SqlDataAdapter(cmd);
                        adapter.Fill(data);
                        note += String.Format("[{0}连接正常] ",account.Name);
                    }
                    catch (Exception ex)
                    {
                        note += String.Format("[{0}连接失败:{1}] ", account.Name, ex.Message);
                    }
                }
                Message(MessageType.Note, note, (DateTime.Now - now).TotalMilliseconds);
                return true;
            }
            catch (Exception ex)
            {
                Message(MessageType.Error, ex.Message, 0);
                return false;
            }
        }

        public void Message(MessageType type, String note)
        {
            Message(type, note, 0);
        }

        private void Message(MessageType type, String note, Double span)
        {
            if (MessageEvent != null)
            {
                MessageEvent.Invoke(type, note, span);
            }
        }

        public Dictionary<String, SetOfBook> GetAccounts()
        {
            if (SetOfBooks.Count == 0) FillAccounts();
            return SetOfBooks;
        }

        private void FillAccounts()
        {
            try
            {
                DataSet data = new DataSet();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT A.FText BaseName,A.FText1 IPAdd,A.FText2 DataUser,A.FText3 DataPswd,A.FNumber,A.FName,A.FID FROM KEW_DataBaseEntry A";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(data);
                foreach (DataRow reader in data.Tables[0].Rows)
                {
                    Account account = new Account();
                    SetOfBook book = new SetOfBook();
                    account.ID = reader["FNumber"].ToString();
                    account.Name = reader["FName"].ToString();
                    account.ConnectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Connect Timeout=15",
                        reader["IPAdd"].ToString(), reader["BaseName"].ToString(), reader["DataUser"].ToString(), reader["DataPswd"].ToString());

                    Accounts.Add(reader["FNumber"].ToString(), account);

                    book.FID = reader["FID"].ToString();
                    book.FNumber = reader["FNumber"].ToString();
                    book.FName = reader["FName"].ToString();
                    SetOfBooks.Add(book.FID, book);
                }
            }
            catch (Exception ex)
            {
                Message(MessageType.Error, ex.Message, 0);
            }
        }
        public Recordset ExeBase(String sql)
        {
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(recordset.Data);

                recordset.Enable = true;
                recordset.Message = "";
                Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
            }
            catch (Exception ex)
            {
                recordset.Enable = false;
                recordset.Message = sql + "   :" + ex.Message;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeBase(String sql, SqlParameter[] parameters)
        {
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = _connection;
                SqlCommand cmd = new SqlCommand();
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        pContent += String.Format("\r\n[{0}]:{1}", parameters[i].ParameterName, parameters[i].Value);
                    }
                }
            
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(recordset.Data);

                recordset.Enable = true;
                recordset.Message = "";
                Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
            }
            catch (Exception ex)
            {
                recordset.Enable = false;
                recordset.Message = sql + "   :" + ex.Message;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeProcedure(String accountid, String sql)
        {
            if (Accounts.Count == 0) FillAccounts();
            DateTime curtime = DateTime.Now;
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();
            if (Accounts.ContainsKey(accountid))
            {
                Account account = Accounts[accountid];
                
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, new SqlConnection(account.ConnectionString));
                    adapter.Fill(recordset.Data);
                    recordset.Enable = true;

                    Message(MessageType.Note, sql, (DateTime.Now - curtime).TotalMilliseconds);
                }
                catch (Exception ex)
                {
                    recordset.Enable = false;
                    recordset.Message = sql + "   :" + ex.Message;
                    Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }
            }
            else
            {
                recordset.Enable = false;
                recordset.Message = "错误的账套号：" + accountid;
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }

        public Recordset ExeProcedure(String accountid, String sql, SqlParameter[] parameters)
        {
            if (Accounts.Count == 0)
             FillAccounts();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            Recordset recordset = new Recordset();
            recordset.Data = new DataSet();

            if (Accounts.ContainsKey(accountid))
            {
                Account account = Accounts[accountid];
                SqlCommand cmd = new SqlCommand();
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);

                    for (int i = 0; i < parameters.Length; i++)
                    {
                        pContent += String.Format("\r\n[{0}]:{1}", parameters[i].ParameterName, parameters[i].Value);
                    }
                }
                cmd.Connection = new SqlConnection(account.ConnectionString);
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                try
                {
                    adapter.Fill(recordset.Data);
                    recordset.Enable = true;
                    Message(MessageType.Note, sql + pContent, (DateTime.Now - curtime).TotalMilliseconds);
                }
                catch (Exception ex)
                {
                    recordset.Enable = false;
                    recordset.Message = sql + "    :" + ex.Message;
                    Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }

                adapter.Dispose();
                adapter = null;
                cmd.Dispose();
                cmd = null;
            }
            else
            {
                recordset.Enable = false;
                recordset.Message = "错误的账套号";
                Message(MessageType.Error, recordset.Message, (DateTime.Now - curtime).TotalMilliseconds);
            }

            return recordset;
        }


        public Dictionary<string, SalesAccount> ExeSalesAccount(String accountid, String strAccountISN)
        {
            //字典—销售账户
            Dictionary<string, SalesAccount> dsa = new Dictionary<string, SalesAccount>();
            if (Accounts.Count == 0)
                FillAccounts();
            DateTime curtime = DateTime.Now;
            String pContent = String.Empty;
            Recordset recordset = new Recordset();
            DataSet data = new DataSet();
            if (Accounts.ContainsKey(accountid))
            {
                Account account = Accounts[accountid];
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = new SqlConnection(account.ConnectionString);
                cmd.CommandText = "SELECT FText1 ISN, FID AccountFID, FBillNo AccountNo From KEW_SalesAccount WHERE FText1 in (" + strAccountISN + ")";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                try
                {
                    adapter.Fill(data);
                    foreach (DataRow reader in data.Tables[0].Rows)
                    {
                        SalesAccount sa = new SalesAccount();
                        sa.AccountISN = reader["ISN"].ToString();
                        sa.AccountFID = reader["AccountFID"].ToString();
                        sa.AccountNo = reader["AccountNo"].ToString();
                        dsa.Add(sa.AccountISN, sa);

                    }
                }
                catch (Exception ex)
                {
                    dsa = null;
                    Message(MessageType.Error, ex.Message, (DateTime.Now - curtime).TotalMilliseconds);
                }
            }
            return dsa;
        }

    }

}
